package org.iabako.server.dao.business;

import org.iabako.server.dao.JpaDAO;
import org.iabako.shared.entity.business.Service;
import org.iabako.shared.entity.user.Enterprise;
import org.springframework.stereotype.Repository;

import javax.persistence.Query;
import java.util.List;

/**
 * Created by jose on 17/03/14.
 */
@Repository("serviceDAO")
public class ServiceDAO extends JpaDAO<Long, Service> {

    @Override
    protected String getUniqueNumberVerificationQuery() {
        return "SELECT s.id FROM Service s WHERE s.number = :number AND s.enterprise IN (:enterprises)";
    }

    @Override
    protected String getLastNumberQuery() {
        return "SELECT MAX(s.number) FROM Service s WHERE s.enterprise IN (:enterprises)";
    }

    public List<Object[]> getNamesBySuggestion(String suggestion) {
        String query = "SELECT id, name, number FROM Service WHERE enterprise IN (:enterprises) AND" +
                "(name LIKE :suggestion OR number LIKE :suggestion)";
        return (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprises()).
                setParameter("suggestion", "%" + suggestion + "%").
                setMaxResults(10).getResultList();
    }

    public List<Service> getServices() {
        String queryString = "SELECT service FROM Service service WHERE service.enterprise IN (:enterprises)";
        List<Service> services = entityManager.createQuery(queryString).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprises()).getResultList();

        return services == null || services.isEmpty() ? null : services;
    }

    //Used for demo
    public Service getByNumber(Enterprise enterprise, String number) {
        String queryString = "SELECT service FROM Service service WHERE service.enterprise = :enterprise AND service.number = :number";
        return (Service) entityManager.createQuery(queryString).
                setParameter("enterprise", enterprise).setParameter("number", number).getSingleResult();
    }

    public Double getLastTax() {
        String queryString = "SELECT s.taxPercent FROM Service s WHERE s.taxPercent IS NOT NULL AND " +
                "s.enterprise IN (:enterprises) order by s.id desc";
        Query query = entityManager.createQuery(queryString);
        query.setMaxResults(1);
        List<Double> result = (List<Double>) query.setParameter("enterprises", getSessionUser().getEnterprise().getAllRelatedEnterprises()).getResultList();
        return result == null || result.isEmpty() ? null : result.get(0);
    }

    public void deleteAttachedObjects(Long id) {

        String query = "DELETE FROM TrackingObject to WHERE to.service.id = :serviceId";
        entityManager.createQuery(query).setParameter("serviceId", id).executeUpdate();

        //delete orphan sales
        query = "DELETE FROM Installment installment WHERE installment.sale IN (SELECT sale FROM SaleProductService sps WHERE sps.service.id = :serviceId)";
        entityManager.createQuery(query).setParameter("serviceId", id).executeUpdate();

        query = "DELETE FROM Payment payment WHERE payment.sale IN (SELECT sale FROM SaleProductService sps WHERE sps.service.id = :serviceId)";
        entityManager.createQuery(query).setParameter("serviceId", id).executeUpdate();

        query = "DELETE FROM TrackingObject to WHERE to.sale IN (SELECT sale FROM SaleProductService sps WHERE sps.service.id = :serviceId)";
        entityManager.createQuery(query).setParameter("serviceId", id).executeUpdate();

        query = "SELECT sale.id FROM SaleProductService sps WHERE sps.service.id = :serviceId";
        List<Long> saleIdList = entityManager.createQuery(query).setParameter("serviceId", id).getResultList();

        if(saleIdList == null || saleIdList.isEmpty()){
            //No sales found with this service
            return;
        }

        query = "DELETE FROM SaleProductService productsServices WHERE productsServices.sale.id IN (:saleIdList)";
        entityManager.createQuery(query).setParameter("saleIdList", saleIdList).executeUpdate();

        query = "DELETE FROM Sale sale WHERE sale.id IN (:saleIdList)";
        entityManager.createQuery(query).setParameter("saleIdList", saleIdList).executeUpdate();
    }
}
